/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.objectsearch.sqlsearch;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import org.objectsearch.parsers.ClassParserEngine;
import org.objectsearch.settings.LibSettings;

/**
 * this class provides access for generating querys and preparedstatements to
 * create the objects in the database and retrieve lists of objects
 * 
 * @author Carlos
 */
public class SqlSearch {

	/**
	 * the main connection to the database
	 */
	private Connection con;
	/**
	 * contains the main engine wich generate the querys
	 */
	private ClassParserEngine engine;
	/**
	 * contains an arrayList of dataTypes wich is used to generate the query
	 * using comparators
	 */
	@SuppressWarnings("unchecked")
	private ArrayList list = new ArrayList();
	/**
	 * is the value wich determines if the search will use likes when making the
	 * string comparisions
	 */
	private boolean like = false;

	/**
	 * the constructor of the sqlsearch class
	 * 
	 * @param connection
	 *            contains the connection in the
	 * @param engine
	 *            the main engine that contains the parameters to work the
	 *            object
	 */
	public SqlSearch(Connection connection, ClassParserEngine engine) {
		this.con = connection;
		this.engine = engine;
	}

	/**
	 * sets the arraylist of the dataTypes used for build the query
	 * 
	 * @param list
	 *            the list of datatypes containing the comparators for the
	 *            database
	 */
	@SuppressWarnings("unchecked")
	public void setDataTypes(ArrayList list) {
		this.list = list;
	}

	/**
	 * deletes all the datatypes contained in the search engine
	 */
	public void deleteAllDataTypes() {
		this.list.clear();
	}

	/**
	 * retrieves the value of the like in the search
	 * 
	 * @return the boolean wich determines if the search will use like in
	 *         comparing strings
	 */
	public boolean isLike() {
		return like;
	}

	/**
	 * sets the value of the like in the search
	 * 
	 * @param like
	 *            determines if the search will use like in comparing strings if
	 *            true the search will compare with a like comparator
	 */
	public void setLike(boolean like) {
		this.like = like;
	}

	/**
	 * builds a query using the object specified by the user and the properties
	 * set in the object using the mapping specified by the java document
	 * 
	 * @param object
	 *            the object wich contains the parameters for a seach in the
	 *            database
	 * @param orders
	 *            the fields to order in the query
	 * @return the string for creating a prepared statement
	 * @throws java.lang.Exception
	 *             is thrown if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public String buildQuery(Object object, String[] orders) throws Exception {
		StringBuffer query = new StringBuffer("select  ");
		String tableName = engine.getTableName(object);
		String[] fieldNames = engine.getFieldNames(object);
		for (int i = 0; i < fieldNames.length; i++) {
			String fieldName = fieldNames[i];
			if (i != 0) {
				query.append(",");
			}
			query.append(tableName).append(".").append(fieldName);
		}
		query.append(" from ").append(tableName).append(" ");
		Hashtable objectHashValues = engine.getObjectHashValues(object);
		Enumeration enumeration = objectHashValues.keys();
		boolean first = true;
		while (enumeration.hasMoreElements()) {
			String key = (String) enumeration.nextElement();
			Object value = objectHashValues.get(key);
			if (first) {
				query.append(" where ");
				first = false;
			} else {
				query.append(" and ");
			}
			if (isLike() && value instanceof String) {
				query.append("lower(").append(key).append(")").append(
						" like ? ");
			} else {
				query.append(key).append(" = ? ");
			}
		}
		Iterator iterator = this.list.iterator();
		while (iterator.hasNext()) {
			DataType type = (DataType) iterator.next();
			Object value = type.getValue();
			String fieldName = type.getFieldName();
			String comparator = type.getComparator();
			if (first) {
				query.append(" where ");
				first = false;
			} else {
				query.append(" and ");
			}
			if (comparator.equalsIgnoreCase("like")) {
				query.append(" lower(").append(fieldName).append(") ").append(
						comparator).append(" ? ");
			} else {
				if (value == null) {
					query.append(fieldName).append(" ").append(comparator)
							.append(" null ");
				} else {
					query.append(fieldName).append(" ").append(comparator)
							.append(" ? ");
				}

			}
		}
		if (orders != null) {
			String[] realOrders = engine.getRealOrdersBy(object, orders);
			if (realOrders != null) {
				boolean f0 = true;
				for (int i = 0; i < realOrders.length; i++) {
					String order = realOrders[i];
					if (f0) {
						query.append(" order by ");
						f0 = false;
					} else {
						query.append(" , ");
					}
					query.append(order);
				}
			}
		}
		if (LibSettings.DEBUG) {
			System.out.println(query.toString());
		}
		return query.toString();
	}

	/**
	 * sets the values for the parameters of the preparedstatement
	 * 
	 * @param psmt
	 *            is the preparedstatement wich is used to create the search
	 * @param object
	 *            the main object in the search it is used to get the properties
	 *            to set the preparedstatement
	 * @throws java.lang.Exception
	 *             if error occurs
	 */
	@SuppressWarnings("unchecked")
	public void buildSearchWithParameters(PreparedStatement psmt, Object object)
			throws Exception {
		Hashtable objectHashValues = engine.getObjectHashValues(object);
		Hashtable properties = engine.getProperties(object);
		Enumeration keyEnum = objectHashValues.keys();
		int paramNumber = 1;
		while (keyEnum.hasMoreElements()) {
			String key = (String) keyEnum.nextElement();
			Object value = objectHashValues.get(key);
			ObjectProperty prop = (ObjectProperty) properties.get(key);
			if (prop.getType().equals("java.util.Date")) {
				long date = ((java.util.Date) value).getTime();
				psmt.setDate(paramNumber, new java.sql.Date(date));
			} else if (prop.getType().equals("java.sql.Clob")) {
				byte[] array = ((String) value).getBytes();
				int length = ((String) value).length();
				InputStream in = new ByteArrayInputStream(array);
				psmt.setAsciiStream(paramNumber, in, length);
			} else if (prop.getType().equals("java.lang.String")) {
				if (this.isLike()) {
					String param = (String) value;
					if (param.indexOf("%") == -1) {
						param = "%" + param.toLowerCase() + "%";
						psmt.setObject(paramNumber, param);
					} else {
						psmt.setObject(paramNumber, value);
					}
				} else {
					psmt.setObject(paramNumber, value);
				}
			} else {
				psmt.setObject(paramNumber, value);
			}
			paramNumber++;
		}
		Iterator iterator = this.list.iterator();
		while (iterator.hasNext()) {
			DataType dataType = (DataType) iterator.next();
			@SuppressWarnings("unused")
			String key = dataType.getFieldName();
			Object value = dataType.getValue();
			String comparator = dataType.getComparator();
			if (value == null) {
				// psmt.setNull(paramNumber, Types.NULL);
				// psmt.setNull(paramNumber, Types.VARCHAR );
			} else if (value instanceof java.util.Date) {
				long date = ((java.util.Date) value).getTime();
				psmt.setDate(paramNumber, new java.sql.Date(date));
			} else if (value instanceof java.sql.Clob) {
				String val = (String) value;
				int length = val.length();
				byte[] data = val.getBytes();
				InputStream in = new ByteArrayInputStream(data);
				psmt.setAsciiStream(paramNumber, in, length);
			} else if (value instanceof String) {
				if (comparator.equalsIgnoreCase("like")) {
					String param = (String) value;
					if (param.indexOf("%") == -1) {
						param = "%" + param.toLowerCase() + "%";
						psmt.setObject(paramNumber, param);
					} else {
						psmt.setObject(paramNumber, value);
					}
				} else {
					psmt.setObject(paramNumber, value);
				}
			} else {
				psmt.setObject(paramNumber, value);
			}
			paramNumber++;
		}
	}

	/**
	 * retrieves the results as an arraylist
	 * 
	 * @param rs
	 *            contains the resultset that will retrieve and convert them to
	 *            objects
	 * @param object
	 *            the object that is going to be used to create the arraylist
	 * @return an arraylist containing the objects retrieved from the resultset
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getResults(ResultSet rs, Object object) throws Exception {
		ArrayList arrayList = new ArrayList();
		ResultSetMetaData metadata = rs.getMetaData();
		String columnNames[] = new String[metadata.getColumnCount()];
		for (int i = 0; i < columnNames.length; i++) {
			columnNames[i] = metadata.getColumnName(i + 1);
		}

		Hashtable properties = engine.getProperties(object);
		Class _class = object.getClass();
		while (rs.next()) {
			Object o = _class.newInstance();
			buildPropertiesFromResultSet(rs, columnNames, properties, o);
			arrayList.add(o);
		}
		return arrayList;
	}

	/**
	 * set the properties of an object using the resultset and the properties of
	 * the object
	 * 
	 * @param rs
	 *            the resultset retrieved by the query in the database
	 * @param columnNames
	 *            the name of the columns in the database
	 * @param properties
	 *            the properties of the object used
	 * @param o
	 *            the object that will be filled with the resultset
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	@SuppressWarnings("unchecked")
	private void buildPropertiesFromResultSet(ResultSet rs,
			String[] columnNames, Hashtable properties, Object o)
			throws Exception {
		for (int i = 0; i < columnNames.length; i++) {
			ObjectProperty prop = (ObjectProperty) properties
					.get(columnNames[i]);
			if (prop != null) {
				String columnName = prop.getDbEquivalent();
				Object result = rs.getObject(columnName);
				if (result instanceof java.sql.Clob) {
					Clob clob = (Clob) result;
					result = clob.getSubString(1, (int) clob.length());
				} else if (result instanceof Number) {
					result = new Integer(((Number) result).intValue());
				} else if (prop.getType().equals("java.math.BigDecimal")
						&& result instanceof String) {
					result = new BigDecimal((String) result);
				}
				if (result != null) {
					engine.setObjectFromProperty(o, result, prop.getName());
				}

			}
		}
	}

	/**
	 * constructs the preparedstatement for inserting the object
	 * 
	 * @param object
	 *            the object wich is going to be inserted in the database
	 * @return the preparedstatement that contains an insert
	 * @throws java.lang.Exception
	 *             occurs if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public PreparedStatement buildInsertStatement(Object object)
			throws Exception {
		Hashtable hash = engine.getObjectHashValues(object);
		StringBuffer query = new StringBuffer("insert into ");
		StringBuffer queryValues = new StringBuffer();
		String tableName = engine.getTableName(object);
		query.append(tableName).append(" ( ");
		Hashtable properties = engine.getProperties(object);
		Enumeration keys = hash.keys();
		boolean first = true;
		while (keys.hasMoreElements()) {
			String key = (String) keys.nextElement();
			if (first) {
				first = false;
				queryValues.append(" ? ");
			} else {
				query.append(" , ");
				queryValues.append(", ? ");
			}
			query.append(key);
		}
		queryValues.append(" ) ");
		query.append(" ) values ( ");
		query.append(queryValues.toString());
		if (LibSettings.DEBUG) {
			System.out.println(query.toString());
		}
		Enumeration keyEnum = hash.keys();
		PreparedStatement psmt = con.prepareStatement(query.toString());
		int parameterNumber = 1;
		while (keyEnum.hasMoreElements()) {
			String key = (String) keyEnum.nextElement();
			Object value = hash.get(key);
			ObjectProperty prop = (ObjectProperty) properties.get(key);
			if (prop.getType().equalsIgnoreCase("java.sql.Clob")) {
				byte[] data = ((String) value).getBytes();
				int length = ((String) value).length();
				InputStream in = new ByteArrayInputStream(data);
				psmt.setAsciiStream(parameterNumber, in, length);
			} else if (prop.getType().equalsIgnoreCase("java.util.Date")) {
				long date = ((java.util.Date) value).getTime();
				psmt.setDate(parameterNumber, new java.sql.Date(date));
			} else {
				psmt.setObject(parameterNumber, value);
			}
			parameterNumber++;
		}
		if (LibSettings.DEBUG) {
			System.out.println(psmt.toString());
		}
		return psmt;
	}

	/**
	 * constructs the preparedstatement for delete the object
	 * 
	 * @param object
	 *            the object wich is going to be deleted in the database
	 * @return the preparedstatement that contains an delete statement
	 * @throws java.lang.Exception
	 *             occurs if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public PreparedStatement buildDeleteStatement(Object object)
			throws Exception {
		String table = engine.getTableName(object);
		StringBuffer deleteSQL = new StringBuffer("delete from ");
		deleteSQL.append(table).append("  where ");
		Hashtable hash = engine.getKeys(object);
		Enumeration keys = hash.keys();
		boolean first = true;
		while (keys.hasMoreElements()) {
			String key = (String) keys.nextElement();
			@SuppressWarnings("unused")
			Object value = hash.get(key);
			if (first) {
				first = false;
			} else {
				deleteSQL.append(" , ");
			}
			deleteSQL.append(key).append(" = ? ");
		}
		if (LibSettings.DEBUG) {
			System.out.println(deleteSQL.toString());
		}
		PreparedStatement psmt = this.con
				.prepareStatement(deleteSQL.toString());
		Enumeration keyset = hash.keys();
		int parameterNumber = 1;
		while (keyset.hasMoreElements()) {
			String key = (String) keyset.nextElement();
			Object value = hash.get(key);
			psmt.setObject(parameterNumber, value);
			parameterNumber++;
		}
		if (LibSettings.DEBUG) {
			System.out.println(psmt.toString());
		}
		return psmt;
	}

	/**
	 * constructs the preparedstatement for updating the object in the database
	 * 
	 * @param object
	 *            the object wich is going to be updated in the database
	 * @return the preparedstatement that contains an update in the database
	 * @throws java.lang.Exception
	 *             occurs if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public PreparedStatement buildUpdateStatement(Object object)
			throws Exception {
		Hashtable keys = engine.getKeys(object);
		Hashtable fields = engine.getFields(object);
		Hashtable properties = engine.getProperties(object);
		String tableName = engine.getTableName(object);
		StringBuffer updateQuery = new StringBuffer("update ");
		updateQuery.append(tableName).append(" ");
		Enumeration fieldEnum = fields.keys();
		boolean f0 = true;
		while (fieldEnum.hasMoreElements()) {
			String key = (String) fieldEnum.nextElement();
			if (f0) {
				f0 = false;
				updateQuery.append(" set ");
			} else {
				updateQuery.append(" , ");
			}
			updateQuery.append(key).append(" = ? ");
		}
		Enumeration keyEnum = keys.keys();
		boolean f1 = true;
		while (keyEnum.hasMoreElements()) {
			String key = (String) keyEnum.nextElement();
			if (f1) {
				f1 = false;
				updateQuery.append(" where ");
			} else {
				updateQuery.append(" and ");
			}
			updateQuery.append(key).append(" = ? ");
		}
		if (LibSettings.DEBUG) {
			System.out.println(updateQuery);
		}
		PreparedStatement psmt = this.con.prepareStatement(updateQuery
				.toString());
		int parameterCount = 1;
		Enumeration hashFields = fields.keys();
		while (hashFields.hasMoreElements()) {
			String key = (String) hashFields.nextElement();
			Object value = fields.get(key);
			ObjectProperty prop = (ObjectProperty) properties.get(key);
			if (prop.getType().equalsIgnoreCase("java.util.Date")) {
				long date = ((java.util.Date) value).getTime();
				psmt.setDate(parameterCount, new java.sql.Date(date));
			} else if (prop.getType().equalsIgnoreCase("java.sql.Clob")) {
				ByteArrayInputStream in = new ByteArrayInputStream(
						((String) value).getBytes());
				int length = ((String) value).length();
				psmt.setAsciiStream(parameterCount, in, length);
			} else {
				psmt.setObject(parameterCount, value);
			}
			parameterCount++;
		}
		Enumeration hashKeys = keys.keys();
		while (hashKeys.hasMoreElements()) {
			String key = (String) hashKeys.nextElement();
			Object value = keys.get(key);
			psmt.setObject(parameterCount, value);
			parameterCount++;
		}
		if (LibSettings.DEBUG) {
			System.out.println(psmt.toString());
		}
		return psmt;
	}
}
